Como exibir os resultados de uma procedure do Microsoft SQL Server no Excel

Importante: para fazer esta funcionalidade funcionar perfeitamente é necessário ter 2 acessos fundamentais:

1-Acesso ao local de rede onde o servidor MS SQL está instalado.
   Para isto exige um usuário e senha da rede local.

2-Acesso ao Servidor MS SQL
   A conexão será feita pelo Excel utilizando um componente externo que necessita a autorização do MS SQL para executar a procedure.
   Portanto é necessário usuário e senha da rede local, se o servidor SQL utilizar segurança integrada, ou usuário e senha do SQL se o modo de autenticação do SQL Server não admitir autenticação por segurança integrada.

3-Se nossa conexão nativa tem acesso a rede local do servidor MS SQL podemos simplificar o uso da ferramenta utilizando o login de rede onde a máquina já está conectada.
   Portanto o primeiro login de rede pode ser omitido quando já fizemos o login mesmo domínio do MS SQL Server.
   Contudo o usuário e senha do MS SQL é obrigatório e para facilitar podemos colocar esta senha e usuário diretamente no Excel, mas isto é um risco de segurança que deve ser evitado.

Importante: Esta ferramenta (VBA) ainda é a ferramenta mais fácil de bolar um ataque ao Windows.
   Sempre que receber uma planilha com VBA certifique-se que a pessoa que a enviou é de confiança e mesmo assim tenha cuidado pois é fácil inserir códigos maliciosos mesmo durante o tráfego da planilha por redes inseguras.

Aba do desenvolvedor

O Excel tem uma aba para facilitar a vida dos desenvolvedores. É a aba 'Desenvolvedor' e se você não está vendo ela clique no menu '‘Arquivo'’, lá embaixo no rodapé da página selecione ‘Opções’. Veja imagem abaixo.

Clique no item ‘Personalizar Faixa de opções’ e selecione a caixa ‘Desenvolvedor’ ( clique no item que aparece um v verde dentro dele indicando selecionado’

Importante: Depois feita esta configuração sempre que você abrir o Excel no menu teremos a aba desenvolvedor acessível.

Construindo a aplicação

4-Como disse no item 2 o acesso ao servidor pelo Excel se dará através de um componente que é instalado por default quando instalamos o Excel no Windows.

Ele se chama '‘Microsoft Activex Data Objects Library'.
Da versão 2.8 para cima irá funcionar.
Este componente tem que ser compatível com a versão do banco de dados que estamos conectando.

5-Abra O Excel e crie uma planilha em vazio para trabalharmos.

6-Clique na aba ‘Desenvolvedor’ e clique em ‘Visual Basic’. Veja imagem abaixo:

As macros do Excel são feitas em ‘Visual Basic’. Uma linguagem rudimentar e antiquada, mas capaz de acessar todos os recursos do Windows.

7-Será aberta a página de desenvolvimento onde faremos toda a programação.

8-Vamos solicitar o acesso do componente ‘Microsoft Data Activex Objects Library.
   Clique na aba ‘Ferramentas’ e em seguida na opção ‘Referências’.
   Procure o item ‘Microsoft e selecione ele clicando no box.Veja imagem abaixo:

Clique no botão ‘OK’ para prosseguir.

9-Neste ponto você deve estar na tela inicial de desenvolvimento VBA. Veja imagem abaixo:

10-Clique no menu '‘Inserir'’ e selecione '‘Módulo'. Veja tela abaixo:

11-Agora você esta na janela de edição do código do módulo. Veja imagem abaixo:

12-Copie e cole o código abaixo. Esta será a função que executará a pesquisa.

Option Explicit

Public Sub FazPesquisa() 'precisa ser public para que a planilha execute esta função neste módulo
Dim rs As New ADODB.Recordset 'componente que envia/recebe dados do SQL
Dim strPesq As String 'pesquisa sql a ser feita
Dim strServidorSQL As String 'servidor SQL
Dim strBaseDadosSQL As String 'Base de dados do Servidor SQL
Dim strUsuarioBDSQL As String 'Usuário do MS SQL
Dim strSenhaBDSQL As String 'Senha do usuário do MS SQL
Dim strConexaoSQL As String 'dados da conexão com o sql completo
Dim conDB As New ADODB.Connection 'conexão com o servidor SQL

On Local Error GoTo erro1 'em caso de erro ir para...

'conectando com o servidor - Inicio
If conDB.State = 1 Then conDB.Close 'fechando a conexão se estiver aberta

strServidorSQL = "xxxx" 'O nome ou ip do Servidor na rede ou o seu endereço IP
strBaseDadosSQL = "bdx"
strUsuarioBDSQL = "usrx" 'Deixar em branco para a autenticação só pela rede, via windows
strSenhaBDSQL = "senhax"

If strSenhaBDSQL > "" Then
      strConexaoSQL = "DRIVER={SQL Server};Server=" & strServidorSQL & ";Database=" & strBaseDadosSQL & ";Uid=" & strUsuarioBDSQL & ";Pwd=" & strSenhaBDSQL & ";Connection Timeout=30;"
Else
      strConexaoSQL = "DRIVER={SQL Server};SERVER=" & strServidorSQL & ";Trusted_Connection=yes;DATABASE=" & strBaseDadosSQL 'Autenticação via Windows
End If
conDB.ConnectionTimeout = 30
conDB.Open strConexaoSQL
'conectando com o servidor - Fim

strPesq = "EXEC PROC_X" 'exemplo com parâmetro: "EXEC procx '" & P1 & "'"

rs.Open strPesq, conDB 'realiza a pesquisa / executa a proc

Dim DBPath As String
DBPath = ThisWorkbook.FullName

Range("A1").CopyFromRecordset rs 'enviando os dados para a planilha
rs.Close

conDB.Close 'fechando a conexão

Exit Sub 'fim da pesquisa

erro1:
MsgBox Err.Description 'exibe msg erro
End Sub

13 - Vá na Planilha. Clique no ‘x’ no topo da tela para fechar o código do módulo.

14 – Agora vamos adicionar um botão que chamará a pesquisa.
Clique no menu 'Desenvolvedor'
Clique no item ‘Inserir’ do menu.
Em seguida clique em ‘Botão de Comando da região '‘Controles ActiveX'.
Veja imagem abaixo:

15-Vá na planilha numa região que você acha boa para colocar o botão.
Clique onde deve ficar o topo esquerdo do botão segurando o clique do botão pressionado e arraste até a parte inferior direita do botão...isto dimensiona o tamanho do botão.
Veja imagem abaixo:

16-Se ficou grande ou pequeno clique no botão e vá com o mouse em suas laterais e redimensione ele clicando na bolinha e arrastando para fora se quiser aumentar ou para dentro se quiser diminuir.
Veja imagem abaixo:

17-Se quiser mover o botão basta clicar nele (veja que o cursor do mouse muda para uma cruz quando passa sobre o bõtão) e arrastar para onde deseja.

18-Setando as propriedades do botão.
   As propriedades do botão são as características dele como nome, mensagem que exibe (caption), cores, tamanho, etc.
   Você pode deixar assim, mas eu prefiro personalizar já que botão1 não diz nada interessante sobre o que o botão faz.

19-Clique no botão com o botão direito do mouse sobre o botão e selecione '‘Propriedades'’

20-Vamos alterar o nome do botão...ao invés de 'CommandButton1' vamos chamar ele de 'btnPesquisar'
Na caixa de propriedades no item digite btnPesquisar

21-Vamos mudar o texto da frente do botão...ao invés de ‘CommandButton1’ vamos colocar ‘Realizar a Pesquisa’.
   O processo é o mesmo, na caixa das propriedades no item 'Caption' digite Realizar a Pesquisa.Veja imagem abaixo:

22-Fazendo o botão executar a pesquisa.
Clique no botão com o botão direito do mouse e selecione '‘Exibir Código'
No meio do código :
Private Sub btnPesquisar_Click()
End Sub

Digite
FazPesquisa

Fica assim:

Salve seu trabalho...clique no botão 'Modo Design' da aba desenvolvedor....

23-Testando 1 : Clique no botão para executar a pesquisa e veja o resultado..

Nota : O Excel não faz milagres. Se nos testes a pesquisa demora 5 minutos para ser executada não espere que no Excel demore menos para executar a pesquisa.
   O Excel torna-se dependente do resultado do SQL e por isso irá demorar o mesmo tempo ou, talvez, um pouco mais.

Regra : Mantenha o resultado abaixo de 10.000 linhas. Mais que isso fica pesado para o servidor SQL e para o Excel.

Neste ponto temos os resultados, os dados da proc ou consulta mas não o cabeçalho de cada coluna.

24-Colocando os cabeçalhos nas colunas.

Vá no código do módulo.
Clique :
1-No menu ‘Desenvolvedor’
2-Clique no modo ‘Design’
3-Clique com o botão direito do mouse no botão
4-Selecione o item ‘Exibir código’
5-Clique em módulo ... 2 vezes se necessário

26-Altere o código:

Dim DBPath As String ‘já tem – antigo - guia
DBPath = ThisWorkbook.FullName ‘já tem – antigo - guia
'Copia os campos para o cabeçalho ‘novo-inserir
Dim n As Integer ‘novo-inserir
For n = 0 To rs.Fields.Count – 1 ‘novo-inserir
      Cells(1, n + 1) = rs.Fields(n).Name ‘novo-inserir
Next n ‘novo-inserir
Range("A2").CopyFromRecordset rs 'enviando os dados para a planilha '*1
rs.Close

conDB.Close 'fechando a conexão ‘já tem – antigo - guia

Exit Sub 'fim da pesquisa ‘já tem – antigo - guia

Legendas : Já existia. Novo

Cuidado: Se você usa o word para salvar texto ele altera apóstrofo por outro caractere...substitua os apóstrofes do word.

Nota : *1 : antes o código era Range("A1").CopyFromRecordset rs mas como na linha 1 colocamos os cabeçalhos das colunas agora temos que colocar os resultados a partir da linha 2 por isso o código mudou para Range("A2").CopyFromRecordset rs

27-Está melhor, mas que tal colocar uma cor de fundo no cabeçalho da tabela...vai melhorar muito a aparência. Logo após o Next n do loop for anterior acrescente o código:

Insira o código logo acima do loop for já existente.
'colocando a cor de fundo
For n = 0 To rs.Fields.Count - 1
Cells(1, n + 1).Interior.ColorIndex = 8 'azul claro
Next n
Fica mais ou menos assim:

'Copia os campos para o cabeçalho
Dim n As Integer
For n = 0 To rs.Fields.Count - 1
Cells(1, n + 1) = rs.Fields(n).Name
Next n

'colocando a cor de fundo
For n = 0 To rs.Fields.Count - 1
Cells(1, n + 1).Interior.ColorIndex = 8 'azul claro
Next n

Range("A2").CopyFromRecordset rs 'enviando os dados para a planilha
rs.Close

A cor de fundo foi definida pela linha:
Cells(1, n + 1).Interior.ColorIndex = 8
Onde 8 é a cor de fundo azul claro.
As cores disponíveis são:

Veja se o processo está bom ou se você precisa adequar ele para sua necessidade.